from django.db import models
from django.contrib.auth import get_user_model
from django.utils import timezone

User = get_user_model()


class DatabaseInstance(models.Model):
    """数据库实例"""
    name = models.CharField(max_length=100, verbose_name='实例名称')
    host = models.CharField(max_length=255, verbose_name='主机地址')
    port = models.IntegerField(default=3306, verbose_name='端口')
    database_type = models.CharField(
        max_length=50,
        choices=[
            ('mysql', 'MySQL'),
            ('postgresql', 'PostgreSQL'),
            ('oracle', 'Oracle'),
            ('sqlserver', 'SQL Server'),
            ('mongodb', 'MongoDB'),
        ],
        default='mysql',
        verbose_name='数据库类型'
    )
    username = models.CharField(max_length=100, verbose_name='用户名')
    password = models.CharField(max_length=255, verbose_name='密码')  # 实际应用中应加密存储
    database_name = models.CharField(max_length=100, verbose_name='数据库名')
    description = models.TextField(blank=True, verbose_name='描述')
    is_active = models.BooleanField(default=True, verbose_name='是否启用')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    updated_at = models.DateTimeField(auto_now=True, verbose_name='更新时间')

    class Meta:
        verbose_name = '数据库实例'
        verbose_name_plural = '数据库实例'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.name} ({self.host}:{self.port})"


class SQLRequest(models.Model):
    """SQL变更请求"""
    STATUS_CHOICES = [
        ('draft', '草稿'),
        ('pending', '待审核'),
        ('approved', '已批准'),
        ('rejected', '已拒绝'),
        ('executing', '执行中'),
        ('executed', '已执行'),
        ('failed', '执行失败'),
        ('withdrawn', '已撤回'),
    ]

    REQUEST_TYPE_CHOICES = [
        ('query', '查询'),
        ('dml', 'DML变更'),
        ('ddl', 'DDL变更'),
        ('other', '其他'),
    ]

    title = models.CharField(max_length=200, verbose_name='请求标题')
    description = models.TextField(verbose_name='请求描述')
    sql_content = models.TextField(verbose_name='SQL内容')
    request_type = models.CharField(
        max_length=20,
        choices=REQUEST_TYPE_CHOICES,
        default='query',
        verbose_name='请求类型'
    )
    database_instance = models.ForeignKey(
        DatabaseInstance,
        on_delete=models.CASCADE,
        verbose_name='目标数据库'
    )
    requester = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name='sql_requests',
        verbose_name='申请人'
    )
    reviewer = models.ForeignKey(
        User,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='reviewed_sql_requests',
        verbose_name='审核人'
    )
    status = models.CharField(
        max_length=20,
        choices=STATUS_CHOICES,
        default='pending',
        verbose_name='状态'
    )
    review_comment = models.TextField(blank=True, verbose_name='审核意见')
    execution_result = models.TextField(blank=True, verbose_name='执行结果')
    executor = models.ForeignKey(
        User,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name='executed_sql_requests',
        verbose_name='执行人'
    )
    priority = models.CharField(
        max_length=20,
        choices=[
            ('low', '低'),
            ('normal', '普通'),
            ('high', '高'),
            ('urgent', '紧急'),
        ],
        default='normal',
        verbose_name='优先级'
    )
    expected_execution_time = models.DateTimeField(null=True, blank=True, verbose_name='期望执行时间')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
    reviewed_at = models.DateTimeField(null=True, blank=True, verbose_name='审核时间')
    executed_at = models.DateTimeField(null=True, blank=True, verbose_name='执行时间')
    withdrawn_at = models.DateTimeField(null=True, blank=True, verbose_name='撤回时间')

    class Meta:
        verbose_name = 'SQL变更请求'
        verbose_name_plural = 'SQL变更请求'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.title} - {self.get_status_display()}"


class SQLQueryHistory(models.Model):
    """SQL查询历史"""
    user = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name='执行用户')
    database_instance = models.ForeignKey(
        DatabaseInstance,
        on_delete=models.CASCADE,
        verbose_name='数据库实例'
    )
    sql_content = models.TextField(verbose_name='SQL内容')
    execution_time = models.FloatField(verbose_name='执行时间(秒)')
    rows_affected = models.IntegerField(default=0, verbose_name='影响行数')
    is_success = models.BooleanField(default=True, verbose_name='是否成功')
    error_message = models.TextField(blank=True, verbose_name='错误信息')
    ip_address = models.GenericIPAddressField(verbose_name='IP地址')
    user_agent = models.TextField(blank=True, verbose_name='用户代理')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='执行时间')

    class Meta:
        verbose_name = 'SQL查询历史'
        verbose_name_plural = 'SQL查询历史'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.user.username} - {self.database_instance.name} - {self.created_at}"


class SQLAuditLog(models.Model):
    """SQL审计日志"""
    ACTION_CHOICES = [
        ('create_request', '创建请求'),
        ('review_request', '审核请求'),
        ('execute_request', '执行请求'),
        ('withdraw_request', '撤回请求'),
        ('query_execute', '执行查询'),
    ]

    user = models.ForeignKey(User, on_delete=models.CASCADE, verbose_name='操作用户')
    action = models.CharField(
        max_length=50,
        choices=ACTION_CHOICES,
        verbose_name='操作类型'
    )
    target_type = models.CharField(max_length=50, verbose_name='目标类型')
    target_id = models.IntegerField(verbose_name='目标ID')
    description = models.TextField(verbose_name='操作描述')
    ip_address = models.GenericIPAddressField(verbose_name='IP地址')
    user_agent = models.TextField(blank=True, verbose_name='用户代理')
    created_at = models.DateTimeField(auto_now_add=True, verbose_name='操作时间')

    class Meta:
        verbose_name = 'SQL审计日志'
        verbose_name_plural = 'SQL审计日志'
        ordering = ['-created_at']

    def __str__(self):
        return f"{self.user.username} - {self.get_action_display()} - {self.created_at}"
